package com.kuhh.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.kuhh.dao.ICollegeDao;
import com.kuhh.pojo.College;
import com.kuhh.utils.JdbcUtils;

public class CollegeDaoImpl implements ICollegeDao{
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	@Override
	public List<College> getCollegeList() {
		// TODO Auto-generated method stub
		List<College> list = new ArrayList<College>();
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_college order by create_time desc";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				College college = new College();
				college.setCollegeNo(rs.getString(1));
				college.setCollegeName(rs.getString(2));
				college.setCreateTime(rs.getDate(3));
				college.setUpdateTime(rs.getDate(4));
				list.add(college);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public College getCollegeByNo(String collegeNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_college where college_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, collegeNo);
			rs = ps.executeQuery();
			while(rs.next()) {
				College college = new College();
				college.setCollegeNo(rs.getString(1));
				college.setCollegeName(rs.getString(2));
				college.setCreateTime(rs.getDate(3));
				college.setUpdateTime(rs.getDate(4));
				return college;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return null;
	}

	@Override
	public boolean updateCollege(College college) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update tb_college set college_name = ?,update_time = ? where college_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, college.getCollegeName());
			ps.setDate(2, new Date(college.getUpdateTime().getTime()));
			ps.setString(3, college.getCollegeNo());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean addCollege(College college) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into tb_college values(?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, college.getCollegeNo());
			ps.setString(2, college.getCollegeName());
			ps.setDate(3, new Date(college.getCreateTime().getTime()));
			ps.setDate(4, new Date(college.getUpdateTime().getTime()));
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean deleteCollegeByNo(String collegeNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "delete from tb_college where college_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, collegeNo);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

}
